import traceback

import flask
import requests
from flask import request, jsonify, g
from functools import wraps
from datetime import datetime
from sqlalchemy.orm import joinedload
import jwt
from global_scope import get_db, SECRET_KEY, IMAGE_BED_TOKEN, TAG_SEP, boxs_sheet_name, configuration_sheet_name, \
    ips_sheet_name
from project_model import TokenRecord, Box
import pandas as pd
from project_model import Contact, FieldStack, FieldArea, BoxTag, BoxType, Image
from global_scope import areas_sheet_name
from global_scope import field_stacks_sheet_name
from global_scope import contacts_sheet_name
from global_scope import box_tags_sheet_name
from global_scope import box_types_sheet_name
from global_scope import images_sheet_name
from global_scope import IMAGE_SEP, IP_SEP
from project_model import Configuration
from global_scope import get_init_configuration
from common_tools import to_dict
from global_scope import leads_sheet_name
from project_model import Lead, Ip
from global_scope import get_limiter

db = get_db()


def token_required(f):
    """
    基于JWT的后端认证装饰器。

    :param f: 被装饰的函数
    :return: 装饰后的函数
    """

    @wraps(f)
    def decorated(*args, **kwargs):
        request.logger.info(f"start jwt token auth")
        auth_header = request.headers.get('Authorization')
        if not auth_header:
            return jsonify({"error": "Token is missing"}), 401

        request.logger.info("Authorization exists")
        try:
            token = auth_header.split(" ")[1]
        except IndexError:
            request.logger.error(f"Token is invalid, Authorization is {auth_header}")
            return jsonify({"error": "Token is invalid"}), 401
        except Exception as e:
            tb = traceback.format_exc()
            request.trace_logger.error(f"Exception occurred: {str(e)}\n{tb}")
            return jsonify({"error": "Server error"}), 500

        request.logger.info("Token exists in request header")
        token_record = TokenRecord.query.filter_by(token=token).first()
        if not token_record:
            request.logger.warn("Token does not exist in database")
            return jsonify({"error": "Invalid token"}), 401

        request.logger.info("Token exists in database")
        try:
            data = jwt.decode(token, SECRET_KEY, algorithms=["HS256"])
            if datetime.utcnow() > datetime.fromtimestamp(data['exp']):
                db.session.delete(token_record)
                db.session.commit()
                request.logger.warn(f"Token expired: {data}")
                return jsonify({"error": "Token has expired"}), 401
            g.user_id = data["id"]
            g.current_role = data["current_role"]
            g.token = token_record
        except jwt.ExpiredSignatureError:
            db.session.delete(token_record)
            db.session.commit()
            return jsonify({"error": "Token has expired"}), 401
        except jwt.InvalidTokenError:
            return jsonify({"error": "Invalid token"}), 401
        except Exception as e:
            tb = traceback.format_exc()
            request.trace_logger.error(f"Exception occurred: {str(e)}\n{tb}")
            return jsonify({"error": "Server error"}), 500

        return f(*args, **kwargs)

    return decorated


def log_operations(func):
    """
    记录操作日志的装饰器。

    :param func: 被装饰的函数
    :return: 装饰后的函数
    """

    @wraps(func)
    def wrapper(*args, **kwargs):
        request.logger.info(f"Request start")
        try:
            result = func(*args, **kwargs)
            request.logger.info(f"{func.__name__} executed successfully")
            return result
        except Exception as e:
            tb = traceback.format_exc()
            request.trace_logger.error(f"Exception occurred: {str(e)}\n{tb}")
            return jsonify({"error": "Server error"}), 500

    return wrapper


def build_permission_tree(data, parent_id=None):
    """
    递归构建权限树的辅助函数。

    :param data: 权限数据
    :param parent_id: 父权限ID
    :return: 权限树
    """
    tree = []
    for item in data:
        if item.parentId == parent_id:
            children = build_permission_tree(data, item.id)
            item_dict = item.__dict__.copy()
            item_dict.pop('_sa_instance_state', None)  # Remove SQLAlchemy instance state
            if children:
                item_dict['children'] = children
            tree.append(item_dict)
    return tree


def get_contacts_df() -> pd.DataFrame:
    contacts = Contact.query.all()
    data = [{
        "name": contact.name,
        "phone": contact.phone,
        "address": contact.address,
        "qq": contact.qq,
        "email": contact.email,
        "remark": contact.remark,
        "images": IMAGE_SEP.join([img.imageUrl for img in contact.images])
    } for contact in contacts]
    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_contacts(file, req: flask.Request) -> (int, int):
    sheet_name = contacts_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_contacts = 0
    updated_contacts = 0

    for _, row in df.iterrows():
        name = row.get('name')
        phone = row.get('phone')
        address = row.get('address')
        qq = row.get('qq')
        email = row.get('email')
        remark = row.get('remark')
        image_urls = row.get('images', '').split(IMAGE_SEP)

        contact = Contact.query.filter_by(name=name, phone=phone).first()

        if contact:
            # Update existing contact
            contact.address = address
            contact.qq = qq
            contact.email = email
            contact.remark = remark
            contact.images.clear()
            for url in image_urls:
                url = url.strip()
                if not url:
                    continue
                image = Image.query.filter_by(imageUrl=url).first()
                if image is None:
                    image = Image(image_url=url)
                contact.images.append(image)
            updated_contacts += 1
        else:
            # Add new contact
            new_contact = Contact(name=name, phone=phone, address=address, qq=qq, email=email, remark=remark)
            for url in image_urls:
                if not url:
                    continue
                image = Image.query.filter_by(imageUrl=url).first()
                if image is None:
                    image = Image(image_url=url)
                new_contact.images.append(image)
            db.session.add(new_contact)
            new_contacts += 1

        db.session.commit()
    return new_contacts, updated_contacts


def get_field_stack_df() -> pd.DataFrame:
    field_stacks = FieldStack.query.options(joinedload(FieldStack.contacts), joinedload(FieldStack.images)).all()

    data = []
    for fs in field_stacks:
        contacts_info = ';'.join([f'{c.name}|{c.phone}' for c in fs.contacts])
        images_info = ' '.join([i.imageUrl for i in fs.images])
        data.append({
            "name": fs.name,
            "phone": fs.phone,
            "address": fs.address,
            "contacts_info": contacts_info,
            "images_info": images_info,
            "website": fs.website,
            "remark": fs.remark or ""
        })

    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_field_stacks(file, req: flask.Request) -> (int, int):
    excel_to_contacts(file, req)
    sheet_name = field_stacks_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')

    new_records = 0
    updated_records = 0
    for index, row in df.iterrows():
        name = row['name']
        address = row['address']
        contacts_info = row['contacts_info']
        images_info = row['images_info']

        existing_record = FieldStack.query.filter_by(name=name, address=address).first()
        if existing_record:
            existing_record.phone = row.get('phone', existing_record.phone)
            existing_record.website = row.get('website', existing_record.website)
            existing_record.remark = row.get('remark', existing_record.remark)
            updated_records += 1
        else:
            new_record = FieldStack(
                name=name,
                phone=row.get('phone', ''),
                address=address,
                website=row.get('website', ''),
                remark=row.get('remark', '')
            )
            db.session.add(new_record)
            existing_record = new_record
            new_records += 1
            db.session.commit()

        # Process contacts_info
        contacts_info_list = contacts_info.split(';')
        for contact_info in contacts_info_list:
            if '|' not in contact_info:
                continue
            contact_name, contact_phone = contact_info.split('|')
            contact = Contact.query.filter_by(name=contact_name, phone=contact_phone).first()
            if not contact:
                contact = Contact(name=contact_name, phone=contact_phone)
                db.session.add(contact)
            if contact not in existing_record.contacts:
                existing_record.contacts.append(contact)

        # Process images_info
        images_info_list = images_info.split(IMAGE_SEP)
        for image_url in images_info_list:
            image_url = image_url.strip()
            if not image_url:
                continue
            image = Image.query.filter_by(imageUrl=image_url).first()
            if not image:
                image = Image(image_url=image_url)
                db.session.add(image)
            if image not in existing_record.images:
                existing_record.images.append(image)

        db.session.commit()
    return new_records, updated_records


def get_area_df() -> pd.DataFrame:
    field_areas = FieldArea.query.options(joinedload(FieldArea.images)).all()

    data = []
    for fa in field_areas:
        images_info = ','.join([f'{img.imageUrl}' for img in fa.images])
        data.append({
            "name": fa.name,
            "remark": fa.remark or "",
            "field_stack_name": fa.field_stack.name,
            "field_stack_address": fa.field_stack.address,
            "location": fa.location,
            "container_length": fa.container_length,
            "max_capacity": fa.max_capacity,
            "images_info": images_info
        })

    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_areas(file, req: request) -> (int, int):
    sheet_name = areas_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')

    new_records = 0
    updated_records = 0
    for index, row in df.iterrows():
        name = row['name']
        field_stack_name = row['field_stack_name']
        field_stack_address = row['field_stack_address']
        images_info = row['images_info']
        location = row.get('location', '场内')
        container_length = row.get('container_length', '20英尺')
        max_capacity = row.get('max_capacity')

        # 获取或创建 FieldStack
        field_stack = FieldStack.query.filter_by(name=field_stack_name, address=field_stack_address).first()
        if not field_stack:
            field_stack = FieldStack(name=field_stack_name, address=field_stack_address, remark='导入时创建')
            db.session.add(field_stack)
            db.session.commit()

        # 获取或创建 FieldArea
        field_area = FieldArea.query.filter_by(
            name=name, container_length=container_length, field_stack_id=field_stack.id).first()
        if field_area:
            field_area.remark = row.get('remark', field_area.remark)
            field_area.location = location
            field_area.max_capacity = max_capacity
            updated_records += 1
        else:
            field_area = FieldArea(
                name=name,
                remark=row.get('remark', ''),
                field_stack_id=field_stack.id,
                location=location,
                container_length=container_length,
                max_capacity=max_capacity
            )
            db.session.add(field_area)
            new_records += 1
            db.session.commit()

        # 处理 images_info
        image_urls = images_info.split(IMAGE_SEP)
        for image_url in image_urls:
            image_url = image_url.strip()
            if not image_url:
                continue
            image = Image.query.filter_by(imageUrl=image_url).first()
            if not image:
                image = Image(image_url=image_url)
                db.session.add(image)
            if image not in field_area.images:
                field_area.images.append(image)

        db.session.commit()
    return new_records, updated_records


def check_sheet_exists(file, sheet_name):
    # 使用 ExcelFile 类读取文件
    excel_file = pd.ExcelFile(file)

    # 获取文件中的所有 sheet 名称
    sheet_names = excel_file.sheet_names

    # 检查指定的 sheet 名称是否在列表中
    return sheet_name in sheet_names


def get_box_tags_df() -> pd.DataFrame:
    box_tags = BoxTag.query.all()
    data = []
    for bt in box_tags:
        image_urls = IMAGE_SEP.join([img.imageUrl for img in bt.images])
        data.append({
            "tagName": bt.tagName,
            "remark": bt.remark or "",
            "imageUrls": image_urls
        })
    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_box_tags(file, req: flask.Request) -> (int, int):
    sheet_name = box_tags_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df_box_tags = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_tags = 0
    updated_tags = 0
    # Insert or update BoxTags
    for index, row in df_box_tags.iterrows():
        tagName = row['tagName']
        remark = row["remark"]
        image_urls = row.get("imageUrls", "").split(IMAGE_SEP)
        existing = BoxTag.query.filter_by(tagName=tagName).first()
        if not existing:
            box_tag = BoxTag(tag_name=tagName, remark=remark)
            db.session.add(box_tag)
            new_tags += 1
        else:
            existing.remark = remark
            box_tag = existing
            updated_tags += 1
        images = []
        for url in image_urls:
            url = url.strip()
            if not url:
                continue
            image = Image.query.filter_by(imageUrl=url).first()
            if not image:
                image = Image(image_url=url)
                db.session.add(image)
            images.append(image)
        box_tag.images = images
        db.session.commit()
    return new_tags, updated_tags


def get_box_types_df() -> pd.DataFrame:
    box_types = BoxType.query.all()
    data = []
    for bt in box_types:
        image_urls = IMAGE_SEP.join([img.imageUrl for img in bt.images])
        data.append({
            "typeName": bt.typeName,
            "length": bt.length,
            "box_type": bt.box_type,
            "box_code": bt.box_code,
            "full_name": bt.full_name,
            "remark": bt.remark or "",
            "imageUrls": image_urls
        })
    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_box_types(file, req: request) -> (int, int):
    sheet_name = box_types_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df_box_types = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_types = 0
    updated_types = 0
    # Insert or update BoxTypes
    for index, row in df_box_types.iterrows():
        typeName = row['typeName']
        length = row['length']
        box_type_value = row['box_type']
        box_code = row['box_code']
        full_name = row['full_name']
        remark = row["remark"]
        image_urls = row.get("imageUrls", "").split(IMAGE_SEP)
        existing = BoxType.query.filter_by(typeName=typeName).first()
        if not existing:
            box_type = BoxType(type_name=typeName, remark=remark)
            box_type.length = length
            box_type.box_type = box_type_value
            box_type.box_code = box_code
            box_type.full_name = full_name
            db.session.add(box_type)
            new_types += 1
        else:
            existing.remark = remark
            existing.length = length
            existing.box_type = box_type_value
            existing.box_code = box_code
            existing.full_name = full_name
            box_type = existing
            updated_types += 1
        images = []
        for url in image_urls:
            url = url.strip()
            if not url:
                continue
            image = Image.query.filter_by(imageUrl=url).first()
            if not image:
                image = Image(image_url=url)
                db.session.add(image)
            images.append(image)
        box_type.images = images
        db.session.commit()
    return new_types, updated_types


def get_images_df() -> pd.DataFrame:
    df_images = Image.query.all()
    df_images = pd.DataFrame([{"imageUrl": img.imageUrl, "remark": img.remark or ""} for img in df_images])

    return df_images.astype(str).fillna('')


def excel_to_images(file, req: flask.Request) -> (int, int):
    sheet_name = images_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df_images = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_objs = 0
    updated_objs = 0
    # Insert or update Images
    for index, row in df_images.iterrows():
        imageUrl = row['imageUrl'].strip()
        remark = row["remark"]
        if not imageUrl:
            continue
        existing = Image.query.filter_by(imageUrl=imageUrl).first()
        if not existing:
            img = Image(image_url=imageUrl)
            img.remark = remark
            db.session.add(img)
            new_objs += 1
        else:
            existing.remark = remark
            updated_objs += 1
        db.session.commit()
    return new_objs, updated_objs


def delete_image_from_image_bed(image: Image, req: flask.Request) -> bool:
    """
    每张图片上传后都会获取分配一个唯一id，如5b7153f79dc6d696149d96cb，删除接口用于删除此图片。
    接口地址：https://api.superbed.cn/info/:id
    Bash删除示例
    curl -d 'token=123456&action=delete' https://api.superbed.cn/info/5b7153f79dc6d696149d96cb
    token 使用 gloabl_scope.py 中的 IMAGE_BED_TOKEN
    图片id 在 Image.query.get(image_id).imageUrl 中， 如 `https://pic.imgdb.cn/item/66818bd8d9c307b7e9938e53.png`
    图片id 为 66818bd8d9c307b7e9938e53 获取
    """
    # 从imageUrl中提取图片ID
    image_url_parts = image.imageUrl.split('/')
    # 假设图片URL的格式为：https://pic.imgdb.cn/item/66818bd8d9c307b7e9938e53.png
    image_id_from_url = image_url_parts[-1].split('.')[0]

    # 调用第三方接口删除图片
    delete_url = f'https://api.superbed.cn/info/{image_id_from_url}'
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    data = {'token': IMAGE_BED_TOKEN, 'action': 'delete'}
    response = requests.post(delete_url, data=data, headers=headers)
    if response.status_code != 200:
        req.logger.error(f"delete image from image bed failed, response: {response}")
        return False
    response = response.json()
    if response['err'] != 0:
        req.logger.error(f"delete image from image bed failed, response: {response}")
        return False
    return response['err'] == 0


def get_boxs_df() -> pd.DataFrame:
    boxs = Box.query.all()

    data = []
    for box in boxs:
        data.append({
            "boxId": box.boxId,
            "boxTypeName": f'{box.box_type.length}|{box.box_type.box_type}',
            "boxTags": TAG_SEP.join([tag.tagName for tag in box.tags]),
            "contactName": box.contact.name,
            "contactPhone": box.contact.phone,
            "areaName": box.area.name,
            "location": box.area.location,
            "areaFieldStackName": box.area.field_stack.name,
            "areaFieldStackAddress": box.area.field_stack.address,
            "boxImages": IMAGE_SEP.join([img.imageUrl for img in box.images]),
            "remark": box.remark
        })

    return pd.DataFrame(data).astype(str).fillna('')


def excel_to_boxs(file, req: flask.Request) -> (int, int):
    sheet_name = boxs_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"sheet {sheet_name} not exist")
        return 0, 0
    df_boxs = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_records = 0
    updated_records = 0
    for index, row in df_boxs.iterrows():
        boxId = row['boxId']
        boxTypeName = row['boxTypeName']
        contactName = row['contactName']
        contactPhone = row['contactPhone']
        areaName = row['areaName']
        areaFieldStackName = row['areaFieldStackName']
        areaFieldStackAddress = row['areaFieldStackAddress']
        boxImages = row['boxImages']
        boxTags = row['boxTags']
        remark = row['remark']

        # Step 1: Ensure FieldStack exists
        field_stack = FieldStack.query.filter_by(name=areaFieldStackName, address=areaFieldStackAddress).first()
        if not field_stack:
            field_stack = FieldStack(name=areaFieldStackName, address=areaFieldStackAddress, remark='导入时创建')
            db.session.add(field_stack)
            db.session.commit()

        # Step 2: Ensure BoxType exists
        box_type = None
        boxTypeName = boxTypeName.strip()
        if boxTypeName and '|' in boxTypeName:
            box_length, t = boxTypeName.split('|')
            box_type = BoxType.query.filter_by(length=box_length, box_type=t).first()
        if not box_type:
            box_type = BoxType.query.filter_by(typeName='错误类型').first()
            if box_type is None:
                box_type = BoxType(type_name="错误类型")
                box_type.length = "错误长度"
                box_type.full_name = "暂不明确"
                box_type.box_code = "暂不明确"
                box_type.box_type = "暂不明确"
                box_type.remark = "暂不明确"
                db.session.add(box_type)
            box_type.remark = f"{box_type.remark}: {boxId} {boxTypeName} 查无此类型"
            db.session.commit()

        # Step 3: Ensure FieldArea exists
        field_area = FieldArea.query.filter_by(
            name=areaName, field_stack_id=field_stack.id).first()
        if (not field_area) or (
                field_area.container_length != "不区分长度" and
                field_area.container_length != box_type.length):
            field_area = FieldArea(
                name=f"{areaName}", container_length=box_type.length,
                remark=f'导入时创建', field_stack_id=field_stack.id)
            db.session.add(field_area)

            field_area.remark = f"{box_type.remark}: {boxId} {boxTypeName} 查无此类型"
            db.session.commit()

        # Step 4: Ensure Contact exists
        contact = Contact.query.filter_by(name=contactName, phone=contactPhone).first()
        if not contact:
            contact = Contact(name=contactName, phone=contactPhone, remark='导入时创建')
            db.session.add(contact)
            db.session.commit()

        # Step 5: Insert or update Box
        box = Box.query.filter_by(boxId=boxId).first()
        if box:
            box.boxType_id = box_type.id
            box.contact_id = contact.id
            box.area_id = field_area.id
            box.remark = remark
            updated_records += 1
        else:
            box = Box(box_id=boxId, box_type_id=box_type.id, contact_id=contact.id, area_id=field_area.id,
                      remark=remark)
            db.session.add(box)
            db.session.commit()
            new_records += 1

        # Step 6: Process Images
        box.images = []
        image_urls = boxImages.split(IMAGE_SEP)
        for image_url in image_urls:
            image_url = image_url.strip()
            if image_url:
                image = Image.query.filter_by(imageUrl=image_url).first()
                if not image:
                    image = Image(image_url=image_url)
                    db.session.add(image)
                    db.session.commit()
                box.images.append(image)

        # Step 7: Process Tags
        box.tags = []
        tag_names = boxTags.split(TAG_SEP)
        for tag_name in tag_names:
            tag_name = tag_name.strip()
            if not tag_name:
                continue
            tag = BoxTag.query.filter_by(tagName=tag_name).first()
            if not tag:
                tag = BoxTag(tag_name=tag_name)
                db.session.add(tag)
                db.session.commit()
            box.tags.append(tag)
        db.session.commit()
    return new_records, updated_records


def clear_all_infos(req: flask.Request):
    try:
        db.session.query(Box).delete()
        db.session.query(Contact).delete()
        db.session.query(FieldArea).delete()
        db.session.query(FieldStack).delete()
        db.session.query(Image).delete()
        db.session.query(BoxTag).delete()
        db.session.query(BoxType).delete()
        db.session.query(Configuration).delete()
        db.session.commit()
    except Exception as e:
        t = traceback.format_exc()
        req.trace_logger.error(f"clear_all_info failed: {e}\n{t}")
    finally:
        ""


def get_boxs_df2() -> pd.DataFrame:
    boxs = Box.query.all()

    data = []
    for box in boxs:
        data.append({
            "boxId": box.boxId,
            "boxTypeName": f'{box.box_type.length}|{box.box_type.box_type}',
            "boxTags": TAG_SEP.join([tag.tagName for tag in box.tags]),
            "contactName": box.contact.name,
            "contactPhone": box.contact.phone,
            "areaName": box.area.name,
            "location": box.area.location,
            "max_capacity": box.area.max_capacity,
            "areaFieldStackName": box.area.field_stack.name,
            "areaFieldStackAddress": box.area.field_stack.address,
            "boxImages": IMAGE_SEP.join([img.imageUrl for img in box.images]),
            "remark": box.remark
        })

    return pd.DataFrame(data).astype(str).fillna('')


def get_configurations_df() -> pd.DataFrame:
    configurations_df = Configuration.query.all()
    configurations_df = pd.DataFrame([to_dict(
        name=conf.name, value=conf.value, remark=conf.remark, CreatTime=conf.createTime,
        updateTime=conf.updateTime
    ) for conf in configurations_df])

    return configurations_df.astype(str).fillna('')


def excel_to_configurations(file, req):
    sheet_name = configuration_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"Sheet {sheet_name} not exist")
        return 0, 0

    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_records = 0
    updated_records = 0

    for index, row in df.iterrows():
        config = Configuration.query.filter_by(name=row['name']).first()
        if config:
            config.value = row['value']
            config.remark = row['remark']
            config.updateTime = datetime.utcnow()
            updated_records += 1
        else:
            new_config = Configuration(
                name=row['name'],
                value=row['value'],
                remark=row['remark']
            )
            db.session.add(new_config)
            new_records += 1
        db.session.commit()
    return new_records, updated_records


def get_configuration(name, req):
    try:
        config = Configuration.query.filter_by(name=name).first()
        if config:
            return config.value
        else:
            return get_init_configuration(name)
    except Exception as e:
        tb = traceback.format_exc()
        req.logger.error(f"get_configuration failed: {e}")
        req.trace_logger.error(f"error occurred:{e}|{tb}")
        return get_init_configuration(name)
    finally:
        ""


def get_leads_df() -> pd.DataFrame:
    leads_df = Lead.query.all()
    leads_df = pd.DataFrame([to_dict(
        from_source=conf.from_source,
        ppn=conf.ppn,
        pn=conf.pn,
        cc=conf.cc,
        pi=conf.pi,
        remark=conf.remark,
        times=conf.times,
        ips=IP_SEP.join([f'{ip.ip};{ip.city};{ip.province};{ip.country}' for ip in conf.ips]),
        CreatTime=conf.createTime,
        updateTime=conf.updateTime
    ) for conf in leads_df])

    return leads_df.astype(str).fillna('')


def excel_to_leads(file, req):
    sheet_name = leads_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"Sheet {sheet_name} not exist")
        return 0, 0

    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_records = 0
    updated_records = 0

    for _, row in df.iterrows():
        lead = Lead.query.filter_by(pn=row['pn']).first()
        if not lead:
            lead = Lead(
                from_source=row['from_source'],
                ppn=row['ppn'],
                pn=row['pn'],
                cc=row['cc'],
                pi=row['pi'],
                remark=row['remark'],
                times=row['times'],
            )
            db.session.add(lead)
            new_records += 1

        ips = row['ips'].split('|')
        for ip_str in ips:
            ip_data = ip_str.split(';')
            ip = Ip.query.filter_by(ip=ip_data[0]).first()
            if not ip:
                ip = Ip(
                    ip=ip_data[0],
                    city=ip_data[1],
                    province=ip_data[2],
                    country=ip_data[3]
                )
                db.session.add(ip)
            if ip not in lead.ips:
                lead.ips.append(ip)

    db.session.commit()
    return new_records, updated_records


def get_ips_df() -> pd.DataFrame:
    ips_df = Ip.query.all()
    ips_df = pd.DataFrame([to_dict(
        ip=conf.ip, country=conf.country, province=conf.province, city=conf.city, times=conf.times,
        li=conf.li, remark=conf.remark, createTime=conf.createTime, updateTime=conf.updateTime
    ) for conf in ips_df])
    return ips_df.astype(str).fillna('')


def excel_to_ips(file, req):
    sheet_name = ips_sheet_name
    if not check_sheet_exists(file, sheet_name):
        req.logger.info(f"Sheet {sheet_name} not exist")
        return 0, 0

    df = pd.read_excel(file, sheet_name=sheet_name, dtype=str).fillna('')
    new_records = 0
    updated_records = 0

    for _, row in df.iterrows():
        ip = Ip.query.filter_by(ip=row['ip']).first()
        if not ip:
            ip = Ip(ip=row['ip'], country=row['country'], province=row['province'],
                    city=row['city'], li=row['li'], remark=row['remark'], times=row['times'])
            db.session.add(ip)
            new_records += 1
    db.session.commit()
    return new_records, updated_records


def rate_limit(limit):
    limiter = get_limiter()

    def decorator(f):
        return limiter.limit(limit)(f)

    return decorator
